from flask import Flask, render_template, request, redirect, url_for, session, flash, jsonify
import pymysql
import hashlib
import json
from datetime import datetime, timedelta
from functools import wraps

# 导入数据库配置
from mysql import DB_CONFIG

app = Flask(__name__)
app.secret_key = 'your-secret-key-here'  # 请更改为安全的密钥

# 管理员账号配置
ADMIN_USERS = {
    'admin': 'abf0ec7e667380f7976189fad249a178',  # QWEasd123的MD5值
    'manager': 'abf0ec7e667380f7976189fad249a178'  # manager的MD5值
}

def get_db_connection():
    """获取数据库连接"""
    return pymysql.connect(**DB_CONFIG)

def login_required(f):
    """登录验证装饰器"""
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'admin_user' not in session:
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function

@app.route('/')
def index():
    """首页重定向到仪表板"""
    if 'admin_user' in session:
        return redirect(url_for('dashboard'))
    return redirect(url_for('login'))

@app.route('/login', methods=['GET', 'POST'])
def login():
    """管理员登录"""
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        
        # 验证用户名和密码
        password_md5 = hashlib.md5(password.encode()).hexdigest()
        
        if username in ADMIN_USERS and ADMIN_USERS[username] == password_md5:
            session['admin_user'] = username
            flash('登录成功！', 'success')
            return redirect(url_for('dashboard'))
        else:
            flash('用户名或密码错误！', 'error')
    
    return render_template('login.html')

@app.route('/logout')
def logout():
    """退出登录"""
    session.pop('admin_user', None)
    flash('已退出登录', 'info')
    return redirect(url_for('login'))

@app.route('/dashboard')
@login_required
def dashboard():
    """仪表板"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        # 获取统计数据
        stats = {}
        
        # 用户总数
        cursor.execute("SELECT COUNT(*) FROM users WHERE status != '删除'")
        stats['total_users'] = cursor.fetchone()[0]
        
        # 今日新增用户
        cursor.execute("SELECT COUNT(*) FROM users WHERE DATE(created_at) = CURDATE() AND status != '删除'")
        stats['today_new_users'] = cursor.fetchone()[0]
        
        # 总积分
        cursor.execute("SELECT SUM(userPoints) FROM users WHERE status != '删除'")
        result = cursor.fetchone()[0]
        stats['total_points'] = result if result else 0
        
        # 今日营收
        cursor.execute("""
            SELECT SUM(amount) FROM payment_orders 
            WHERE DATE(created_at) = CURDATE() AND payment_status = 1
        """)
        result = cursor.fetchone()[0]
        stats['today_revenue'] = float(result) if result else 0.0
        
        # 总营收
        cursor.execute("""
            SELECT SUM(amount) FROM payment_orders 
            WHERE payment_status = 1
        """)
        result = cursor.fetchone()[0]
        stats['total_revenue'] = float(result) if result else 0.0
        
        # 待支付订单数
        cursor.execute("SELECT COUNT(*) FROM payment_orders WHERE payment_status = 0")
        stats['pending_orders'] = cursor.fetchone()[0]
        
        return render_template('dashboard.html', stats=stats)
        
    except Exception as e:
        flash(f'获取统计数据失败: {str(e)}', 'error')
        return render_template('dashboard.html', stats={})
    finally:
        cursor.close()
        conn.close()

@app.route('/users')
@login_required
def users():
    """用户列表"""
    page = request.args.get('page', 1, type=int)
    per_page = 20
    search = request.args.get('search', '')
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        # 构建查询条件
        where_clause = "WHERE status != '删除'"
        params = []
        
        if search:
            where_clause += " AND (username LIKE %s OR userPhone LIKE %s OR userEmail LIKE %s)"
            search_param = f"%{search}%"
            params.extend([search_param, search_param, search_param])
        
        # 获取总数
        count_sql = f"SELECT COUNT(*) FROM users {where_clause}"
        cursor.execute(count_sql, params)
        total = cursor.fetchone()[0]
        
        # 获取用户列表
        offset = (page - 1) * per_page
        list_sql = f"""
            SELECT userid, username, userPhone, userEmail, userCode, userAge, 
                   userGender, userCity, userPoints, userLevel, status, created_at
            FROM users {where_clause}
            ORDER BY created_at DESC
            LIMIT %s OFFSET %s
        """
        params.extend([per_page, offset])
        cursor.execute(list_sql, params)
        users_list = cursor.fetchall()
        
        # 计算分页信息
        total_pages = (total + per_page - 1) // per_page
        
        return render_template('users.html', 
                             users=users_list, 
                             page=page, 
                             total_pages=total_pages, 
                             total=total,
                             search=search)
        
    except Exception as e:
        flash(f'获取用户列表失败: {str(e)}', 'error')
        return render_template('users.html', users=[], page=1, total_pages=1, total=0, search='')
    finally:
        cursor.close()
        conn.close()

@app.route('/user/<int:user_id>')
@login_required
def user_detail(user_id):
    """用户详情"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        # 获取用户信息
        cursor.execute("""
            SELECT userid, username, userPhone, userEmail, userCode, userAvatarUrl,
                   userData, userAge, userGender, userSignature, userTags, userLikes,
                   userFollowers, userPoints, userHobbies, userCity, userImgUrls,
                   userFrequentPlaces, userBirthday, userLevel, status, extraData,
                   created_at, last_login, updated_at
            FROM users WHERE userid = %s
        """, (user_id,))
        user = cursor.fetchone()
        
        if not user:
            flash('用户不存在', 'error')
            return redirect(url_for('users'))
        
        # 获取用户积分记录
        cursor.execute("""
            SELECT points_change, points_before, points_after, change_type, 
                   change_reason, created_at
            FROM user_points_log 
            WHERE userid = %s 
            ORDER BY created_at DESC 
            LIMIT 10
        """, (user_id,))
        points_log = cursor.fetchall()
        
        # 获取用户订单记录
        cursor.execute("""
            SELECT order_id, amount, points, order_title, payment_status, 
                   payment_time, created_at
            FROM payment_orders 
            WHERE user_id = %s 
            ORDER BY created_at DESC 
            LIMIT 10
        """, (user_id,))
        orders = cursor.fetchall()
        
        return render_template('user_detail.html', 
                             user=user, 
                             points_log=points_log, 
                             orders=orders)
        
    except Exception as e:
        flash(f'获取用户详情失败: {str(e)}', 'error')
        return redirect(url_for('users'))
    finally:
        cursor.close()
        conn.close()

@app.route('/add_user', methods=['GET', 'POST'])
@login_required
def add_user():
    """新增用户"""
    if request.method == 'POST':
        conn = get_db_connection()
        cursor = conn.cursor()
        
        try:
            # 获取表单数据
            username = request.form['username']
            userPhone = request.form.get('userPhone')
            userEmail = request.form.get('userEmail')
            userCode = int(request.form.get('userCode', 0))
            userAge = request.form.get('userAge', type=int)
            userGender = request.form.get('userGender')
            userCity = request.form.get('userCity')
            userPoints = int(request.form.get('userPoints', 0))
            userSignature = request.form.get('userSignature')
            
            # 插入用户数据
            insert_sql = """
                INSERT INTO users (username, userPhone, userEmail, userCode, userAge, 
                                 userGender, userCity, userPoints, userSignature, status)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, '开启')
            """
            
            cursor.execute(insert_sql, (username, userPhone, userEmail, userCode, 
                                      userAge, userGender, userCity, userPoints, userSignature))
            
            user_id = cursor.lastrowid
            
            # 如果设置了初始积分，记录积分日志
            if userPoints > 0:
                points_log_sql = """
                    INSERT INTO user_points_log 
                    (userid, points_change, points_before, points_after, change_type, 
                     change_reason, operator, status)
                    VALUES (%s, %s, 0, %s, 5, '管理员手动添加', %s, '有效')
                """
                cursor.execute(points_log_sql, (user_id, userPoints, userPoints, session['admin_user']))
            
            conn.commit()
            flash('用户添加成功！', 'success')
            return redirect(url_for('user_detail', user_id=user_id))
            
        except Exception as e:
            conn.rollback()
            flash(f'添加用户失败: {str(e)}', 'error')
        finally:
            cursor.close()
            conn.close()
    
    return render_template('add_user.html')

@app.route('/points')
@login_required
def points():
    """积分情况查看"""
    page = request.args.get('page', 1, type=int)
    per_page = 50
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        # 获取积分统计
        cursor.execute("""
            SELECT 
                SUM(CASE WHEN points_change > 0 THEN points_change ELSE 0 END) as total_earned,
                SUM(CASE WHEN points_change < 0 THEN ABS(points_change) ELSE 0 END) as total_spent,
                COUNT(*) as total_records
            FROM user_points_log WHERE status = '有效'
        """)
        stats = cursor.fetchone()
        
        # 获取积分记录总数
        cursor.execute("SELECT COUNT(*) FROM user_points_log WHERE status = '有效'")
        total = cursor.fetchone()[0]
        
        # 获取积分记录列表
        offset = (page - 1) * per_page
        cursor.execute("""
            SELECT pl.id, u.username, u.userPhone, pl.points_change, 
                   pl.points_before, pl.points_after, pl.change_type, 
                   pl.change_reason, pl.operator, pl.created_at
            FROM user_points_log pl
            JOIN users u ON pl.userid = u.userid
            WHERE pl.status = '有效'
            ORDER BY pl.created_at DESC
            LIMIT %s OFFSET %s
        """, (per_page, offset))
        points_log = cursor.fetchall()
        
        # 计算分页信息
        total_pages = (total + per_page - 1) // per_page
        
        return render_template('points.html', 
                             stats=stats, 
                             points_log=points_log,
                             page=page, 
                             total_pages=total_pages, 
                             total=total)
        
    except Exception as e:
        flash(f'获取积分数据失败: {str(e)}', 'error')
        return render_template('points.html', stats=(0, 0, 0), points_log=[], 
                             page=1, total_pages=1, total=0)
    finally:
        cursor.close()
        conn.close()

@app.route('/revenue')
@login_required
def revenue():
    """营收情况查看"""
    page = request.args.get('page', 1, type=int)
    per_page = 50
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        # 获取营收统计
        cursor.execute("""
            SELECT 
                SUM(CASE WHEN payment_status = 1 THEN amount ELSE 0 END) as total_revenue,
                SUM(CASE WHEN payment_status = 3 THEN refund_amount ELSE 0 END) as total_refund,
                COUNT(CASE WHEN payment_status = 1 THEN 1 END) as success_orders,
                COUNT(CASE WHEN payment_status = 0 THEN 1 END) as pending_orders,
                COUNT(*) as total_orders
            FROM payment_orders
        """)
        stats = cursor.fetchone()
        
        # 获取最近7天的营收数据
        cursor.execute("""
            SELECT DATE(created_at) as date, 
                   SUM(CASE WHEN payment_status = 1 THEN amount ELSE 0 END) as daily_revenue,
                   COUNT(CASE WHEN payment_status = 1 THEN 1 END) as daily_orders
            FROM payment_orders 
            WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
            GROUP BY DATE(created_at)
            ORDER BY date DESC
        """)
        daily_stats = cursor.fetchall()
        
        # 获取订单记录总数
        cursor.execute("SELECT COUNT(*) FROM payment_orders")
        total = cursor.fetchone()[0]
        
        # 获取订单记录列表
        offset = (page - 1) * per_page
        cursor.execute("""
            SELECT po.order_id, u.username, u.userPhone, po.amount, po.points,
                   po.order_title, po.payment_status, po.payment_time, po.created_at
            FROM payment_orders po
            JOIN users u ON po.user_id = u.userid
            ORDER BY po.created_at DESC
            LIMIT %s OFFSET %s
        """, (per_page, offset))
        orders = cursor.fetchall()
        
        # 计算分页信息
        total_pages = (total + per_page - 1) // per_page
        
        return render_template('revenue.html', 
                             stats=stats, 
                             daily_stats=daily_stats,
                             orders=orders,
                             page=page, 
                             total_pages=total_pages, 
                             total=total)
        
    except Exception as e:
        flash(f'获取营收数据失败: {str(e)}', 'error')
        return render_template('revenue.html', stats=(0, 0, 0, 0, 0), daily_stats=[], 
                             orders=[], page=1, total_pages=1, total=0)
    finally:
        cursor.close()
        conn.close()

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=443)